package org.compiere.model;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.Level;

import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class Mhrmovertimeline extends X_hrm_overtime_line{

	/**
	 * 
	 */
	private static final long serialVersionUID = -1445629400867634796L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (Mhrmovertimeline.class);

	public Mhrmovertimeline(Properties ctx) {
		super(ctx);
		
	}
	
	public Mhrmovertimeline(Properties ctx, int hrm_overtime_line_ID,
			String trxName) {
		super(ctx, hrm_overtime_line_ID, trxName);
		
	}

	public Mhrmovertimeline(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
		
	}

public static Mhrmovertimeline getOvertimeRec(int C_BPartner_ID, int hrm_order_ID, Timestamp dt) {
		
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Mhrmovertimeline result = null;
			
		// 
			String sql_ = "SELECT * FROM HRM_OVERTIME_LINE \n " +
							 "WHERE ISACTIVE='Y' \n " +
								"AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n" +
								"AND C_BPARTNER_ID=" + C_BPartner_ID + " \n" +
								"AND HRM_ORDER_ID=" + hrm_order_ID + " \n" +
								"AND TO_DATE('01-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') <=" + DB.TO_DATE(dt) + " \n " + 
								"AND TO_DATE('31-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') >=" + DB.TO_DATE(dt) + " \n " ;							
			try
			{
				pstmt = DB.prepareStatement(sql_,null);				
				rs = pstmt.executeQuery();	
				if(rs.next())
					result = new Mhrmovertimeline(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			
		return result;		
	}

public static int getOvertimeHours(int C_BPartner_ID, Timestamp dt){
	
	if(C_BPartner_ID == 0)
		return 0;
			
	int result = 0;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	// Employee data from the card
	String sql_ = "SELECT SUM(HRM_DAYHOURS)+SUM(HRM_NIGHTHOURS) FROM HRM_OVERTIME_LINE \n " +
					 "WHERE ISACTIVE='Y' \n" +
					    "AND AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
					    "AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
					    "AND TO_DATE('01-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') <=" + DB.TO_DATE(dt) + " \n " +
					    "AND TO_DATE('31-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') >=" + DB.TO_DATE(dt) + " \n " ;
	try
	{
		pstmt = DB.prepareStatement(sql_,null);
		rs = pstmt.executeQuery();
		if(rs.next())
			return rs.getInt(1);		
	}
	catch (SQLException e)
	{
		s_log.log(Level.SEVERE, "product", e);
	}
	finally
	{
		DB.close(rs, pstmt);
		rs = null; pstmt = null;
	}

	return result;
 }

public static BigDecimal getOvertimeSalary(int C_BPartner_ID, Timestamp dt){
	
	if(C_BPartner_ID == 0)
		return null;
			
	BigDecimal result = new BigDecimal(0);
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	// Employee data from the card
	String sql_ = "SELECT SUM(HRM_DAYTOTAL) + SUM(HRM_NIGHTTOTAL) FROM HRM_OVERTIME_LINE \n " +
					 "WHERE ISACTIVE='Y' \n" +
					    "AND AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
					    "AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
					    "AND TO_DATE('01-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') <=" + DB.TO_DATE(dt) + " \n " +
					    "AND TO_DATE('31-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') >=" + DB.TO_DATE(dt) + " \n " ;
	try
	{
		pstmt = DB.prepareStatement(sql_,null);						
		rs = pstmt.executeQuery();
		if(rs.next())
			return rs.getBigDecimal(1);		
	}
	catch (SQLException e)
	{
		s_log.log(Level.SEVERE, "product", e);
	}
	finally
	{
		DB.close(rs, pstmt);
		rs = null; pstmt = null;
	}

	return result;
  }

public static Mhrmovertimeline[] getOvertimeArray(int C_BPartner_ID, Timestamp dt) {
	
	//
    PreparedStatement pstmt = null;
	ResultSet rs = null;
	Mhrmovertimeline result = null;
		
	ArrayList<Mhrmovertimeline> list = new ArrayList<Mhrmovertimeline>();
	// 
	String sql_ = "SELECT * FROM HRM_OVERTIME_LINE \n " +
			         "WHERE ISACTIVE='Y' \n" +
					    "AND AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
					    "AND C_BPARTNER_ID=" + C_BPartner_ID   + " \n " +					    
					    "AND TO_DATE('01-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') <=" + DB.TO_DATE(dt) + " \n " +
					    "AND TO_DATE('31-'||TO_CHAR(DATEFROM,'MM-YYYY'),'DD-MM-YYYY') >=" + DB.TO_DATE(dt) + " \n " ;
		try
		{
			pstmt = DB.prepareStatement(sql_,null);				
			rs = pstmt.executeQuery();	
			while(rs.next()){
				result = new Mhrmovertimeline(Env.getCtx(), rs, null);
				list.add(result);
			}
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}			
		return list.toArray(new Mhrmovertimeline[list.size()]);		
  }

}
